package BS.data;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class User {
    private int Id;
    private String Username;
    private String Address;
    private String Phone;

    public User(int id, String name, String phone, String address) {
        Id = id;
        Username = name;
        Address = address;
        Phone = phone;
    }

    public User() {
    }

    public int getId() {
        return Id;
    }

    public void setId(int id) {
        Id = id;
    }

    public String getUsername() {
        return Username;
    }

    public void setUsername(String name) {
        Username = name;
    }

    public String getAddress() {
        return Address;
    }

    public void setAddress(String address) {
        Address = address;
    }

    public String getPhone() {
        return Phone;
    }

    public void setPhone(String phone) {
        Phone = phone;
    }

    @Override
    public String toString() {
        return "User{" +
                "Id=" + Id +
                ", Username='" + Username + '\'' +
                ", Address='" + Address + '\'' +
                ", Phone='" + Phone + '\'' +
                '}';
    }


    // 添加数据
    public void inter(User user) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/experimen", "root", "531124");
        String sql = "insert into t_user(username,address,phone,id)values(?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(4, user.getId());
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getAddress());
        preparedStatement.setString(3, user.getPhone());
        preparedStatement.execute();
        preparedStatement.close();
        connection.close();
    }



    //数据修改
    public void updateUser(User user) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection coon = null;
        PreparedStatement ps = null;
        coon = DriverManager.getConnection("jdbc:mysql://localhost:3306/experimen", "root", "531124");
        String sql = "update t_user set username=?,address = ?,phone = ? where id = ?";//定义SQL语句，？是占位符需要后期动态为？赋值
        ps = coon.prepareStatement(sql);
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getAddress());
        ps.setString(3, user.getPhone());
        ps.setLong(4, user.getId());
        ps.execute();
        ps.close();
        coon.close();
    }



    //数据删除
    public static void deleteUserById(int id) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection coon = null;
        PreparedStatement ps = null;
        coon = DriverManager.getConnection("jdbc:mysql://localhost:3306/experimen", "root", "531124");
        String sql = "delete  from t_user where id=?";//定义SQL语句，？是占位符需要后期动态为？赋值
        ps = coon.prepareStatement(sql);
        ps.setInt(1, id);
        System.out.println("正在删除数据···");
        ps.execute();
        ps.close();
    }


    //数据查询
    public List<User> selectUserAll() throws SQLException, ClassNotFoundException {
        List<User> list = new ArrayList<User>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;//结果集合对象，用于封装数据库的查询结果
        String sql = "";

        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/experimen", "root", "531124");
        sql = "select * from t_user";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();//执行数据库的查询，并返回查询结果
        while (rs.next()) {//rs.next,让光标向下移动一次并判断下一个元素是否有值，如果有值则返回真进入循环
            User user = new User();
            user.setId(rs.getInt("id"));//将结果集中当前元素的显示列名为id的数据获取出来并设置到user的id属性上
            user.setUsername(rs.getString("username"));
            user.setAddress(rs.getString("address"));
            user.setPhone(rs.getString("phone"));
            list.add(user);
        }
        rs.close();
        ps.close();
        conn.close();
        return list;
    }


    //数据查询
    public User selectById(Integer id) throws SQLException, ClassNotFoundException {
        User user = new User();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;//结果集合对象，用于封装数据库的查询结果
        String sql = "";

        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/experimen", "root", "531124");
        sql = "select * from t_user where id=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        rs = ps.executeQuery();//执行数据库的查询，并返回查询结果
        while (rs.next()) {//rs.next,让光标向下移动一次并判断下一个元素是否有值，如果有值则返回真进入循环
            user.setId(rs.getInt("id"));//将结果集中当前元素的显示列名为id的数据获取出来并设置到user的id属性上
            user.setUsername(rs.getString("username"));
            user.setAddress(rs.getString("address"));
            user.setPhone(rs.getString("phone"));
        }
        rs.close();
        ps.close();
        conn.close();
        return user;
    }

}